import {
  Condition,
  ConditionOperator,
  CONDITION_OPERATOR,
  LogicalOperator,
  LOGICAL_OPERATOR,
  SqlBuilderExtensions,
  SqlExecutor,
  TableEntityMapping
} from './types';

/**
 * 条件构建器
 * 用于构建SQL条件表达式
 */
export class ConditionBuilder<T> {
  /** 条件列表 */
  protected conditions: Condition[] = [];

  /** SQL参数 */
  protected parameters: Record<string, any> = {};

  /** 参数计数器 */
  protected paramCounter = 0;

  /** 表别名 */
  protected tableAlias?: string;

  /** SQL构建器扩展 */
  protected extensions?: SqlBuilderExtensions;

  /** SQL执行器 */
  private executor?: SqlExecutor;

  /** 下一个条件使用的逻辑操作符 */
  private nextLogicalOperator?: LogicalOperator;

  /**
   * 构造函数
   * @param tableAlias 表别名（可选）
   * @param extensions SQL构建器扩展（可选）
   */
  constructor(tableAlias?: string, extensions?: SqlBuilderExtensions) {
    this.tableAlias = tableAlias;
    this.extensions = extensions;
  }

  /**
   * 重置构建器状态
   */
  public reset(): this {
    this.conditions = [];
    this.parameters = {};
    this.paramCounter = 0;
    this.nextLogicalOperator = undefined;
    return this;
  }

  /**
   * 设置下一个条件使用AND连接，或者添加AND WHERE子句
   * 此方法支持两种用法：
   * 1. 无参数调用时用于提高链式调用的可读性
   * 2. 带回调参数时用于添加AND条件组
   * 
   * SQL格式:
   * - 无参数: previous_condition AND next_condition
   * - 有回调: previous_condition AND (callback_conditions)
   * 
   * @example
   * // 无参数用法（链式调用）
   * // 生成: name = 'John' AND age > 18
   * builder.eq('name', 'John').and().gt('age', 18)
   * 
   * // 带回调参数用法（条件组）
   * // 生成: status = 'active' AND (score > 80 OR level > 5)
   * builder.eq('status', 'active')
   *   .and(subBuilder => 
   *     subBuilder.gt('score', 80).or().gt('level', 5)
   *   )
   * 
   * // 嵌套条件组
   * // 生成: status = 'active' AND (department = 'sales' AND (experience > 5 OR certified = true))
   * builder.eq('status', 'active')
   *   .and(b1 => 
   *     b1.eq('department', 'sales')
   *       .and(b2 => 
   *         b2.gt('experience', 5).or().eq('certified', true)
   *       )
   *   )
   * 
   * @param callback 可选的回调函数，用于构建子条件
   * @returns 当前构建器实例，支持链式调用
   */
  public and(callback?: (builder: ConditionBuilder<T>) => void): this {
    // 如果没有提供回调，这只是一个链式调用的助手
    if (!callback) {
      // 不需要实际操作，因为AND是默认的逻辑操作符
      return this;
    }
    
    // 如果提供了回调，功能等同于andWhere
    // 创建临时构建器
    const tempBuilder = this.clone();
    tempBuilder.conditions = [];

    // 执行回调以获取条件
    callback(tempBuilder);

    // 如果有条件
    if (tempBuilder.conditions.length > 0) {
      // 添加条件到当前构建器
      this.conditions.push(...tempBuilder.conditions);

      // 添加参数到当前构建器
      Object.assign(this.parameters, tempBuilder.parameters);
    }

    return this;
  }

  /**
   * 设置下一个条件使用OR连接，或者添加OR WHERE子句
   * 此方法支持两种用法：
   * 1. 无参数调用时设置下一个条件使用OR连接
   * 2. 带回调参数时添加OR条件组
   * 
   * SQL格式:
   * - 无参数: previous_condition OR next_condition
   * - 有回调: previous_condition OR (callback_conditions)
   * 
   * @example
   * // 无参数用法（链式调用）
   * // 生成: name = 'John' OR name = 'Jane'
   * builder.eq('name', 'John').or().eq('name', 'Jane')
   * 
   * // 带回调参数用法（条件组）
   * // 生成: status = 'active' OR (score > 90 AND level > 10)
   * builder.eq('status', 'active')
   *   .or(subBuilder => 
   *     subBuilder.gt('score', 90).and().gt('level', 10)
   *   )
   * 
   * // 复杂条件组合
   * // 生成: status = 'pending' OR (priority = 'high' AND deadline < '2023-12-31')
   * builder.eq('status', 'pending')
   *   .or(b => 
   *     b.eq('priority', 'high').and().lt('deadline', '2023-12-31')
   *   )
   * 
   * @param callback 可选的回调函数，用于构建子条件
   * @returns 当前构建器实例，支持链式调用
   */
  public or(callback?: (builder: ConditionBuilder<T>) => void): this {
    // 如果没有提供回调，只是设置下一个条件的逻辑操作符
    if (!callback) {
      // 如果有条件，更改下一个条件的逻辑操作符
      if (this.conditions.length > 0) {
        this.nextLogicalOperator = LOGICAL_OPERATOR.OR;
      }
      return this;
    }
    
    // 如果提供了回调，功能等同于orWhere
    // 创建临时构建器
    const tempBuilder = this.clone();
    tempBuilder.conditions = [];

    // 执行回调以获取条件
    callback(tempBuilder);

    // 如果有条件
    if (tempBuilder.conditions.length > 0) {
      // 修改第一个条件的逻辑操作符为OR
      tempBuilder.conditions[0].logicalOperator = LOGICAL_OPERATOR.OR;

      // 添加条件到当前构建器
      this.conditions.push(...tempBuilder.conditions);

      // 添加参数到当前构建器
      Object.assign(this.parameters, tempBuilder.parameters);
    }

    return this;
  }

  /**
   * 添加条件
   * 这是一个通用的条件构建方法，是大多数特定条件方法（如eq、gt、like等）的基础
   * SQL格式: field operator value
   * 
   * @example
   * // 生成: age > 18
   * builder.where('age', '>', 18)
   * 
   * // 带表别名: user.status = 'active'
   * builder.where('user.status', '=', 'active')
   * 
   * // 带OR连接: name = 'John' OR email = 'john@example.com'
   * builder.where('name', '=', 'John')
   * builder.where('email', '=', 'john@example.com', 'OR')
   * 
   * @param field 字段名
   * @param operator 操作符
   * @param value 值
   * @param logicalOperator 逻辑操作符（默认为AND）
   * @returns 当前构建器实例，支持链式调用
   */
  public where(
    field: keyof T | string,
    operator: ConditionOperator,
    value: any,
    logicalOperator: LogicalOperator = LOGICAL_OPERATOR.AND
  ): this {
    // 处理字段名
    const processedField = this.processFieldName(field as string);
    
    // 生成参数名
    const paramName = this.generateParamName(processedField);
    
    // 处理值
    const processedValue = this.processValue(value);
    
    // 创建条件
    const condition: Condition = {
      field: processedField,
      operator,
      value: processedValue,
      // 使用nextLogicalOperator，如果设置了的话
      logicalOperator: this.nextLogicalOperator || logicalOperator,
      parameterName: paramName
    };
    
    // 重置nextLogicalOperator
    this.nextLogicalOperator = undefined;
    
    // 添加条件
    this.conditions.push(condition);
    
    // 添加参数（除非是不需要参数的操作符，如IS NULL）
    if (![CONDITION_OPERATOR.IS_NULL, CONDITION_OPERATOR.IS_NOT_NULL].includes(operator)) {
      this.parameters[paramName] = processedValue;
    }
    
    return this;
  }

  /**
   * 添加等于条件
   * SQL格式: field = value
   * 
   * @example
   * // 生成: name = 'John'
   * builder.eq('name', 'John')
   * 
   * // 带表别名: user.name = 'John'
   * builder.eq('user.name', 'John')
   * 
   * @param field 字段名
   * @param value 值
   * @returns 当前构建器实例，支持链式调用
   */
  public eq(field: keyof T | string, value: any): this {
    return this.where(field, CONDITION_OPERATOR.EQ, value);
  }

  /**
   * 添加不等于条件
   * SQL格式: field != value
   * 
   * @example
   * // 生成: status != 'inactive'
   * builder.ne('status', 'inactive')
   * 
   * // 带表别名: user.role != 'guest'
   * builder.ne('user.role', 'guest')
   * 
   * @param field 字段名
   * @param value 值
   * @returns 当前构建器实例，支持链式调用
   */
  public ne(field: keyof T | string, value: any): this {
    return this.where(field, CONDITION_OPERATOR.NE, value);
  }

  /**
   * 添加大于条件
   * SQL格式: field > value
   * 
   * @example
   * // 生成: age > 18
   * builder.gt('age', 18)
   * 
   * // 带表别名: user.score > 80
   * builder.gt('user.score', 80)
   * 
   * @param field 字段名
   * @param value 值
   * @returns 当前构建器实例，支持链式调用
   */
  public gt(field: keyof T | string, value: any): this {
    return this.where(field, CONDITION_OPERATOR.GT, value);
  }

  /**
   * 添加大于等于条件
   * SQL格式: field >= value
   * 
   * @example
   * // 生成: score >= 60
   * builder.gte('score', 60)
   * 
   * // 带表别名: product.price >= 100
   * builder.gte('product.price', 100)
   * 
   * @param field 字段名
   * @param value 值
   * @returns 当前构建器实例，支持链式调用
   */
  public gte(field: keyof T | string, value: any): this {
    return this.where(field, CONDITION_OPERATOR.GTE, value);
  }

  /**
   * 添加小于条件
   * SQL格式: field < value
   * 
   * @example
   * // 生成: price < 100
   * builder.lt('price', 100)
   * 
   * // 带表别名: order.amount < 1000
   * builder.lt('order.amount', 1000)
   * 
   * @param field 字段名
   * @param value 值
   * @returns 当前构建器实例，支持链式调用
   */
  public lt(field: keyof T | string, value: any): this {
    return this.where(field, CONDITION_OPERATOR.LT, value);
  }

  /**
   * 添加小于等于条件
   * SQL格式: field <= value
   * 
   * @example
   * // 生成: quantity <= 50
   * builder.lte('quantity', 50)
   * 
   * // 带表别名: inventory.stock <= 10
   * builder.lte('inventory.stock', 10)
   * 
   * @param field 字段名
   * @param value 值
   * @returns 当前构建器实例，支持链式调用
   */
  public lte(field: keyof T | string, value: any): this {
    return this.where(field, CONDITION_OPERATOR.LTE, value);
  }

  /**
   * 添加IN条件
   * SQL格式: field IN (value1, value2, ...)
   * 
   * @example
   * // 生成: status IN (1, 2, 3)
   * builder.in('status', [1, 2, 3])
   * 
   * // 带表别名: user.role IN ('admin', 'editor')
   * builder.in('user.role', ['admin', 'editor'])
   * 
   * @param field 字段名
   * @param values 值数组
   * @returns 当前构建器实例，支持链式调用
   */
  public in(field: keyof T | string, values: any[]): this {
    return this.where(field, CONDITION_OPERATOR.IN, values);
  }

  /**
   * 添加NOT IN条件
   * SQL格式: field NOT IN (value1, value2, ...)
   * 
   * @example
   * // 生成: category NOT IN ('A', 'B')
   * builder.notIn('category', ['A', 'B'])
   * 
   * // 带表别名: product.id NOT IN (1, 2, 3)
   * builder.notIn('product.id', [1, 2, 3])
   * 
   * @param field 字段名
   * @param values 值数组
   * @returns 当前构建器实例，支持链式调用
   */
  public notIn(field: keyof T | string, values: any[]): this {
    return this.where(field, CONDITION_OPERATOR.NOT_IN, values);
  }

  /**
   * 添加LIKE条件，全模糊匹配
   * SQL格式: field LIKE '%value%'
   * 
   * @example
   * // 生成: name LIKE '%john%'
   * builder.like('name', 'john')
   * 
   * // 带表别名: product.description LIKE '%wireless%'
   * builder.like('product.description', 'wireless')
   * 
   * @param field 字段名
   * @param value 值（自动添加%前后缀）
   * @returns 当前构建器实例，支持链式调用
   */
  public like(field: keyof T | string, value: string): this {
    return this.where(field, CONDITION_OPERATOR.LIKE, `%${value}%`);
  }

  /**
   * 添加LIKE条件（左模糊匹配，即以指定值结尾）
   * SQL格式: field LIKE '%value'
   * 
   * @example
   * // 生成: name LIKE '%son'
   * builder.likeLeft('name', 'son')
   * 
   * // 带表别名: file.path LIKE '%/documents'
   * builder.likeLeft('file.path', '/documents')
   * 
   * @param field 字段名
   * @param value 值（自动添加%前缀）
   * @returns 当前构建器实例，支持链式调用
   */
  public likeLeft(field: keyof T | string, value: string): this {
    return this.where(field, CONDITION_OPERATOR.LIKE, `%${value}`);
  }

  /**
   * 添加LIKE条件（右模糊匹配，即以指定值开头）
   * SQL格式: field LIKE 'value%'
   * 
   * @example
   * // 生成: name LIKE 'jo%'
   * builder.likeRight('name', 'jo')
   * 
   * // 带表别名: email.address LIKE 'admin%'
   * builder.likeRight('email.address', 'admin')
   * 
   * @param field 字段名
   * @param value 值（自动添加%后缀）
   * @returns 当前构建器实例，支持链式调用
   */
  public likeRight(field: keyof T | string, value: string): this {
    return this.where(field, CONDITION_OPERATOR.LIKE, `${value}%`);
  }

  /**
   * 添加NOT LIKE条件
   * SQL格式: field NOT LIKE '%value%'
   * 
   * @example
   * // 生成: name NOT LIKE '%admin%'
   * builder.notLike('name', 'admin')
   * 
   * // 带表别名: product.tags NOT LIKE '%deprecated%'
   * builder.notLike('product.tags', 'deprecated')
   * 
   * @param field 字段名
   * @param value 值（自动添加%前后缀）
   * @returns 当前构建器实例，支持链式调用
   */
  public notLike(field: keyof T | string, value: string): this {
    return this.where(field, CONDITION_OPERATOR.NOT_LIKE, `%${value}%`);
  }

  /**
   * 添加IS NULL条件
   * SQL格式: field IS NULL
   * 
   * @example
   * // 生成: deletedAt IS NULL
   * builder.isNull('deletedAt')
   * 
   * // 带表别名: user.lastLoginDate IS NULL
   * builder.isNull('user.lastLoginDate')
   * 
   * @param field 字段名
   * @returns 当前构建器实例，支持链式调用
   */
  public isNull(field: keyof T | string): this {
    return this.where(field, CONDITION_OPERATOR.IS_NULL, null);
  }

  /**
   * 添加IS NOT NULL条件
   * SQL格式: field IS NOT NULL
   * 
   * @example
   * // 生成: createdAt IS NOT NULL
   * builder.isNotNull('createdAt')
   * 
   * // 带表别名: order.processedDate IS NOT NULL
   * builder.isNotNull('order.processedDate')
   * 
   * @param field 字段名
   * @returns 当前构建器实例，支持链式调用
   */
  public isNotNull(field: keyof T | string): this {
    return this.where(field, CONDITION_OPERATOR.IS_NOT_NULL, null);
  }

  /**
   * 添加BETWEEN条件
   * SQL格式: field BETWEEN min AND max
   * 
   * @example
   * // 生成: age BETWEEN 18 AND 30
   * builder.between('age', 18, 30)
   * 
   * // 带表别名: product.price BETWEEN 100 AND 500
   * builder.between('product.price', 100, 500)
   * 
   * // 日期范围: createdAt BETWEEN '2023-01-01' AND '2023-12-31'
   * builder.between('createdAt', '2023-01-01', '2023-12-31')
   * 
   * @param field 字段名
   * @param min 最小值
   * @param max 最大值
   * @returns 当前构建器实例，支持链式调用
   */
  public between(field: keyof T | string, min: any, max: any): this {
    // 处理字段名
    const processedField = this.processFieldName(field as string);

    // 生成参数名
    const minParamName = this.generateParamName(`${processedField}_min`);
    const maxParamName = this.generateParamName(`${processedField}_max`);

    // 创建条件
    const condition: Condition = {
      field: processedField,
      operator: CONDITION_OPERATOR.BETWEEN,
      value: [min, max],
      logicalOperator: LOGICAL_OPERATOR.AND,
      parameterName: [minParamName, maxParamName]
    };

    // 添加条件
    this.conditions.push(condition);

    // 添加参数
    this.parameters[minParamName] = this.processValue(min);
    this.parameters[maxParamName] = this.processValue(max);

    return this;
  }

  /**
   * 根据对象添加条件
   * 为对象中的每个属性添加等于条件，使用指定的逻辑操作符连接
   * SQL格式: field1 = value1 AND field2 = value2 ...
   * 
   * @example
   * // 生成: id = 1 AND isActive = true
   * builder.whereObject({ id: 1, isActive: true })
   * 
   * // 使用OR连接: id = 1 OR isActive = true
   * builder.whereObject({ id: 1, isActive: true }, 'OR')
   * 
   * @param obj 条件对象，键为字段名，值为字段值
   * @param logicalOperator 逻辑操作符（默认为AND）
   * @returns 当前构建器实例，支持链式调用
   */
  public whereObject(obj: Partial<T>, logicalOperator: LogicalOperator = LOGICAL_OPERATOR.AND): this {
    for (const key in obj) {
      if (Object.prototype.hasOwnProperty.call(obj, key)) {
        const value = obj[key];
        // 等于条件
        this.where(key, CONDITION_OPERATOR.EQ, value, logicalOperator);
      }
    }

    return this;
  }

  /**
   * 构建条件SQL语句
   * @param includeWhere 是否包含WHERE关键字
   * @returns 条件SQL
   */
  public buildConditionsSql(includeWhere: boolean = true): string {
    if (this.conditions.length === 0) {
      return '';
    }

    const conditions: string[] = [];

    for (let i = 0; i < this.conditions.length; i++) {
      const condition = this.conditions[i];
      const { field, operator, parameterName } = condition;

      let conditionSql = '';

      switch (operator) {
        case CONDITION_OPERATOR.EQ:
        case CONDITION_OPERATOR.NE:
        case CONDITION_OPERATOR.GT:
        case CONDITION_OPERATOR.GTE:
        case CONDITION_OPERATOR.LT:
        case CONDITION_OPERATOR.LTE:
          conditionSql = `${field} ${operator} @${parameterName}`;
          break;
        case CONDITION_OPERATOR.LIKE:
        case CONDITION_OPERATOR.NOT_LIKE:
          conditionSql = `${field} ${operator} @${parameterName}`;
          break;
        case CONDITION_OPERATOR.IN:
          conditionSql = `${field} IN (${this.expandArrayParam(parameterName as string, condition.value as any[])})`;
          break;
        case CONDITION_OPERATOR.NOT_IN:
          conditionSql = `${field} NOT IN (${this.expandArrayParam(parameterName as string, condition.value as any[])})`;
          break;
        case CONDITION_OPERATOR.IS_NULL:
          conditionSql = `${field} IS NULL`;
          break;
        case CONDITION_OPERATOR.IS_NOT_NULL:
          conditionSql = `${field} IS NOT NULL`;
          break;
        case CONDITION_OPERATOR.BETWEEN:
          if (parameterName) {
            if (Array.isArray(parameterName)) {
              conditionSql = `${field} BETWEEN @${parameterName[0]} AND @${parameterName[1]}`;
            } else if (typeof parameterName === 'string') {
              const [minParamName, maxParamName] = parameterName.split(',');
              conditionSql = `${field} BETWEEN @${minParamName} AND @${maxParamName}`;
            }
          }
          break;
        case CONDITION_OPERATOR.NOT_BETWEEN:
          if (parameterName) {
            if (Array.isArray(parameterName)) {
              conditionSql = `${field} NOT BETWEEN @${parameterName[0]} AND @${parameterName[1]}`;
            } else if (typeof parameterName === 'string') {
              const [minParamName, maxParamName] = parameterName.split(',');
              conditionSql = `${field} NOT BETWEEN @${minParamName} AND @${maxParamName}`;
            }
          }
          break;
        case CONDITION_OPERATOR.EXISTS:
          conditionSql = `EXISTS (${condition.value})`;
          break;
        case CONDITION_OPERATOR.NOT_EXISTS:
          conditionSql = `NOT EXISTS (${condition.value})`;
          break;
        case CONDITION_OPERATOR.RAW:
          // 原始SQL片段直接使用
          conditionSql = condition.value as string;
          break;
      }

      if (conditionSql) {
        if (i > 0) {
          conditions.push(`${condition.logicalOperator} ${conditionSql}`);
        } else {
          conditions.push(conditionSql);
        }
      }
    }

    return conditions.length ? `${includeWhere ? 'WHERE ' : ''}${conditions.join(' ')}` : '';
  }

  /**
   * 展开数组参数为多个参数
   * @param baseParamName 基础参数名
   * @param values 值数组
   */
  protected expandArrayParam(baseParamName: string, values: any[]): string {
    const params: string[] = [];

    if (!values || !Array.isArray(values) || values.length === 0) {
      return '(NULL)';
    }

    for (let i = 0; i < values.length; i++) {
      const paramName = `${baseParamName}_${i}`;
      params.push(`@${paramName}`);
      this.parameters[paramName] = this.processValue(values[i]);
    }

    return params.join(', ');
  }

  /**
   * 处理字段名
   * @param field 字段名
   */
  protected processFieldName(field: string): string {
    let processedField = field;

    // 如果没有表名或点号，并且有表别名，添加表别名
    if (!processedField.includes('.') && this.tableAlias) {
      processedField = `${this.tableAlias}.${processedField}`;
    }

    // 应用自定义字段处理器
    if (this.extensions && this.extensions.fieldProcessors) {
      for (const processor of this.extensions.fieldProcessors) {
        processedField = processor(processedField, { });
      }
    }

    return processedField;
  }

  /**
   * 处理值
   * @param value 值
   */
  protected processValue(value: any): any {
    let processedValue = value;

    // 应用自定义值处理器
    if (this.extensions && this.extensions.valueProcessors) {
      for (const processor of this.extensions.valueProcessors) {
        processedValue = processor(processedValue, { });
      }
    }

    return processedValue;
  }

  /**
   * 生成唯一参数名
   * @param base 基础名
   */
  protected generateParamName(base: string): string {
    // 清理基础名称（移除表别名和点号）
    const cleanBase = base.replace(/[^a-zA-Z0-9_]/g, '_');

    // 生成唯一名称
    const paramName = `${cleanBase}_${++this.paramCounter}`;

    return paramName;
  }

  /**
   * 克隆当前构建器
   */
  protected clone(): ConditionBuilder<T> {
    const clone = new ConditionBuilder<T>(this.tableAlias, this.extensions);
    clone.conditions = [...this.conditions];
    clone.parameters = { ...this.parameters };
    clone.paramCounter = this.paramCounter;
    clone.nextLogicalOperator = this.nextLogicalOperator;
    return clone;
  }

  /**
   * 获取条件列表
   */
  public getConditions(): Condition[] {
    return [...this.conditions];
  }

  /**
   * 获取参数
   */
  public getParameters(): Record<string, any> {
    return { ...this.parameters };
  }

  /**
   * 字段值不在指定范围内条件
   * SQL格式: field NOT BETWEEN min AND max
   * 
   * @example
   * // 生成: price NOT BETWEEN 100 AND 200
   * builder.notBetween('price', 100, 200)
   * 
   * // 带表别名: product.rating NOT BETWEEN 1 AND 3
   * builder.notBetween('product.rating', 1, 3)
   * 
   * // 日期范围: createdAt NOT BETWEEN '2020-01-01' AND '2020-12-31'
   * builder.notBetween('createdAt', '2020-01-01', '2020-12-31')
   * 
   * @param field 字段名
   * @param min 最小值
   * @param max 最大值
   * @returns 当前构建器实例，支持链式调用
   */
  public notBetween(field: keyof T | string, min: any, max: any): this {
    // 处理字段名
    const processedField = this.processFieldName(field as string);

    // 生成参数名
    const minParamName = this.generateParamName(`${processedField}_min`);
    const maxParamName = this.generateParamName(`${processedField}_max`);

    // 创建条件
    const condition: Condition = {
      field: processedField,
      operator: CONDITION_OPERATOR.NOT_BETWEEN,
      value: [min, max],
      logicalOperator: LOGICAL_OPERATOR.AND,
      parameterName: [minParamName, maxParamName]
    };

    // 添加条件
    this.conditions.push(condition);

    // 添加参数
    this.parameters[minParamName] = this.processValue(min);
    this.parameters[maxParamName] = this.processValue(max);

    return this;
  }

  /**
   * 多个字段与值比较的条件组合
   * SQL格式: field1 = value1 AND field2 = value2 ...
   * 
   * @example
   * // 生成: firstName = 'John' AND lastName = 'Doe'
   * builder.multiEq(['firstName', 'lastName'], ['John', 'Doe'])
   * 
   * // 带表别名: user.city = 'New York' AND user.country = 'USA'
   * builder.multiEq(['user.city', 'user.country'], ['New York', 'USA'])
   * 
   * @param fields 字段名数组
   * @param values 值数组
   * @returns 当前构建器实例，支持链式调用
   */
  public multiEq(fields: Array<keyof T | string>, values: any[]): this {
    if (fields.length !== values.length) {
      throw new Error('Fields and values arrays must have the same length');
    }

    for (let i = 0; i < fields.length; i++) {
      this.eq(fields[i], values[i]);
    }

    return this;
  }

  /**
   * EXISTS子查询条件
   * SQL格式: EXISTS (subquery)
   * 
   * @example
   * // 生成: EXISTS (SELECT 1 FROM orders WHERE orders.userId = users.id)
   * builder.exists('SELECT 1 FROM orders WHERE orders.userId = users.id')
   * 
   * // 带参数:
   * builder.exists(
   *   'SELECT 1 FROM orders WHERE orders.userId = users.id AND orders.status = @status',
   *   { status: 'completed' }
   * )
   * 
   * @param subquerySql 子查询SQL语句
   * @param params 子查询参数
   * @returns 当前构建器实例，支持链式调用
   */
  public exists(subquerySql: string, params?: Record<string, any>): this {
    // 创建条件
    const condition: Condition = {
      field: '',
      operator: CONDITION_OPERATOR.EXISTS,
      value: subquerySql,
      logicalOperator: LOGICAL_OPERATOR.AND
    };

    // 添加条件
    this.conditions.push(condition);

    // 添加参数
    if (params) {
      Object.assign(this.parameters, params);
    }

    return this;
  }

  /**
   * NOT EXISTS子查询条件
   * SQL格式: NOT EXISTS (subquery)
   * 
   * @example
   * // 生成: NOT EXISTS (SELECT 1 FROM blacklist WHERE blacklist.userId = users.id)
   * builder.notExists('SELECT 1 FROM blacklist WHERE blacklist.userId = users.id')
   * 
   * // 带参数:
   * builder.notExists(
   *   'SELECT 1 FROM rejections WHERE rejections.userId = users.id AND rejections.reason = @reason',
   *   { reason: 'spam' }
   * )
   * 
   * @param subquerySql 子查询SQL语句
   * @param params 子查询参数
   * @returns 当前构建器实例，支持链式调用
   */
  public notExists(subquerySql: string, params?: Record<string, any>): this {
    // 创建条件
    const condition: Condition = {
      field: '',
      operator: CONDITION_OPERATOR.NOT_EXISTS,
      value: subquerySql,
      logicalOperator: LOGICAL_OPERATOR.AND
    };

    // 添加条件
    this.conditions.push(condition);

    // 添加参数
    if (params) {
      Object.assign(this.parameters, params);
    }

    return this;
  }

  /**
   * 设置SQL执行器
   * @param executor SQL执行器
   * @returns 当前构建器实例
   */
  public setExecutor(executor: SqlExecutor): this {
    this.executor = executor;
    return this;
  }

  /**
   * 获取SQL执行器
   * @returns SQL执行器实例
   */
  public getExecutor(): SqlExecutor | undefined {
    return this.executor;
  }

  /**
   * 添加原始SQL条件片段
   * 用于添加自定义SQL条件，当内置条件方法无法满足需求时使用
   * SQL格式: raw_sql_fragment
   * 
   * @example
   * // 生成: EXTRACT(YEAR FROM date) = 2023
   * builder.rawWhere('EXTRACT(YEAR FROM date) = 2023')
   * 
   * // 带参数: LOWER(name) = @name
   * builder.rawWhere('LOWER(name) = @name', { name: 'john' })
   * 
   * // 带OR连接: status = 'active' OR DATEDIFF(NOW(), created_at) < 7
   * builder.eq('status', 'active')
   *   .or()
   *   .rawWhere('DATEDIFF(NOW(), created_at) < 7')
   * 
   * // 复杂SQL函数: JSON_EXTRACT(data, '$.preferences.theme') = 'dark'
   * builder.rawWhere("JSON_EXTRACT(data, '$.preferences.theme') = 'dark'")
   * 
   * @param sql 原始SQL片段
   * @param params 可选的参数对象
   * @param logicalOperator 逻辑操作符（默认为AND）
   * @returns 当前构建器实例，支持链式调用
   */
  public rawWhere(
    sql: string, 
    params?: Record<string, any>, 
    logicalOperator: LogicalOperator = LOGICAL_OPERATOR.AND
  ): this {
    // 创建条件
    const condition: Condition = {
      field: '',  // 原始SQL不需要字段名
      operator: CONDITION_OPERATOR.RAW,  // 使用RAW操作符
      value: sql,  // SQL片段作为值
      // 使用nextLogicalOperator，如果设置了的话
      logicalOperator: this.nextLogicalOperator || logicalOperator
    };
    
    // 重置nextLogicalOperator
    this.nextLogicalOperator = undefined;
    
    // 添加条件
    this.conditions.push(condition);
    
    // 添加参数（如果提供）
    if (params) {
      Object.assign(this.parameters, params);
    }
    
    return this;
  }
}
